# coding: utf-8


import os
import set_env_path     # 请勿删除

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

Base = declarative_base()
engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
Session = sessionmaker(bind=engine)
session = Session()


def brands_init():
    """
    create by ld_wu 2017-12-21
    产品类下的品牌初始化
    从原产品中的产品类中的品牌名提取品牌作为品牌的数据
    """
    select_brands = """
        select distinct brand, product_type_id from product_templates;
    """

    insert_brands = """
    insert into 
        brands 
        (product_type_id, brand_name, weight) 
    values
        (:product_type_id, :brand_name, 0)
    """

    select_existed_brands = """
        select id, brand_name, product_type_id from brands;
    """

    brands = session.execute(select_existed_brands).fetchall()
    brand_list = [
        '{}:{}'.format(each.product_type_id, each.brand_name)
        for each in brands
    ]

    result_set = set()

    result = session.execute(select_brands).fetchall()
    for each in result:
        print(each.brand)
        if each.brand == '无':
            continue
        key = '{}:{}'.format(each.product_type_id, each.brand)
        if key in result_set:
            continue
        result_set.add(key)
        if key in brand_list:
            continue
        session.execute(
            insert_brands,
            dict(
                product_type_id=each.product_type_id,
                brand_name=each.brand
            )
        )
    session.commit()


def update_product_brand():
    """
    create by ld_wu 2017-12-21
    产品类下的品牌初始化
    从原产品中的产品类中的品牌名提取品牌作为品牌的数据
    """
    select_brands = """
        select id, brand_name, product_type_id from brands;
    """

    update_product_brand_sql = """
    update 
        product_templates 
    set 
        brand_id=:brand_id
        
    where
        product_type_id=:product_type_id
        and brand=:brand
    """
    result = session.execute(select_brands).fetchall()
    for each in result:
        session.execute(
            update_product_brand_sql,
            dict(
                brand_id=each.id,
                product_type_id=each.product_type_id,
                brand=each.brand_name
            )
        )
        print(each.brand_name)
    session.commit()


def set_default_options():
    """
    更新技术参数options空置为null --> []
    :return:
    """
    update_product_param_sql = """
        update
            product_to_params
        set
            options = '[]'
        WHERE
            options is null
    """

    update_params_sql = """
        update
            product_to_params
        set
            options = '[]'
        WHERE
            options is null
    """
    session.execute(update_product_param_sql)
    session.execute(update_params_sql)
    session.commit()


if __name__ == '__main__':
    brands_init()
    update_product_brand()
    set_default_options()
